package com.pointi.emoServerM;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.pointi.emoTools.SysGuidelineData;
import com.pointi.emoTools.emoJDBC;
import com.pointi.emoTools.vasResult;
import com.pointi.emoTools.vasUnit;

public class JDBC extends emoJDBC
{
	// ____________________________________________________________________________________________________
	// Default

	public void fnPrepareDatabase()
	{
		String query = "" ;
		query = " EXEC sp_create_sysparam_operate_log" ; 
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_sysparam_df_guideline" ; 
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_sysparam_df_exception" ; 
		fnExecuteInsertOrUpdateQuery(query) ;
		sleep(500) ;
	}

	// ____________________________________________________________________________________________________
	// t_callkey_checklist_yyyymm

	public void fnUpdateCheckList(String inColumn, int inValue, int inCallKey)
	{
		String query = "" ;
		query = " UPDATE t_callkey_checklist_" + fnGetYYYYMM() + " " +
				" SET " + inColumn + " = '" + inValue + "' " +
				" WHERE callkey = " + inCallKey ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	// ____________________________________________________________________________________________________
	// t_info_server

	public String fnGetServerInfo(String inServerNo)
	{
		String mPort = "" ;
		String query = "" ;
		query = " SELECT server_port " +
				" FROM t_info_server " +
				" WHERE server_no = " + inServerNo ;
		try {
			ResultSet mRS = null ;
			if ((mRS = fnGetResultSet(query)) != null)
				while (mRS.next()) { mPort = mRS.getString("server_port") ; }
			return mPort ;
		} catch (SQLException se) { System. out.println(se.getMessage()) ; }
		return null ;
	}

	public void fnUpdateServerStatus(CheckStatus inStatus)
	{
		String query = "" ;
		query = " UPDATE t_info_server " +
				" SET server_status = " + inStatus.fnGetServerStatus() + ", server_count = " + inStatus.fnGetServerCount() + " " +
				" WHERE server_no = " + inStatus.fnGetServerNo() + "; " ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	public boolean fnIsServerAlive(String inServerNo)
	{
		String query = "" ;
		query = " SELECT server_alive " +
				" FROM t_info_server " +
				" WHERE server_no = " + inServerNo + " " ;
		try {
			ResultSet mRS = null ;
			if ((mRS = fnGetResultSet(query)) != null)
				while (mRS.next()) { if (mRS.getInt("server_alive") == 1) return true ; }
		} catch (SQLException se) { System. out.println(se.getMessage()) ; }
		return false ;
	}

	// ____________________________________________________________________________________________________
	// t_sysparam_perate_log / 사라질 예정

	public int fnGetOperationSeq()
	{
		int nReturn = 0 ;
		String query = "" ;
		query = " SELECT apply_date, seq " +
				" FROM t_sysparam_operate_log " +
				" ORDER BY apply_date DESC ; " ;
		try {
			SimpleDateFormat new_date = new SimpleDateFormat("yyyy-MM-dd") ;
			String s_check_new_date = new_date.format(new Date()) ;
			Date m_new_date = fnGetStringDateToDate(s_check_new_date) ;
			Date m_old_date = new Date() ;
			Date m_rs_date = new Date() ;
			ResultSet mRS = null ;
			if ((mRS = fnGetResultSet(query)) != null)
				while (mRS.next()) {
					m_rs_date = fnGetStringDateToDate(mRS.getString("apply_date")) ;
					/**/ if (m_new_date.after(m_rs_date)) { nReturn = mRS.getInt("seq") ; break; }
					else if (m_new_date.after(m_rs_date) && m_new_date.before(m_old_date)) { nReturn = mRS.getInt("seq"); break ; }
					else if (m_new_date.equals(m_rs_date)) { nReturn = mRS.getInt("seq") ; break ; }
					else { nReturn = mRS.getInt("seq") ; }
					m_old_date = m_rs_date ;
				}
		} catch(SQLException se) { System.out.println(se.getMessage()) ; }
		return nReturn;
	}

	// ____________________________________________________________________________________________________
	// t_sysparam_df_guideline

	public int fnGetSubTimeRange()
	{
		int nResult = 1 ;
		int n_op_seq = fnGetOperationSeq() ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT top 1 agent_default_subtime_range " +
				" FROM t_sysparam_df_guideline " +
				" WHERE use_yn = 1 " +
				" AND (oplog_seq = " + n_op_seq + " OR oplog_seq IS NULL) ;" ;
		try {
			if ((mRS = fnGetResultSet(query)) != null)
				while(mRS.next()) { nResult = mRS.getInt("agent_default_subtime_range") ; }
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return nResult ;
	}

	public void fnGetSubTimeRange(SysGuidelineData sysGD)
	{
		Statement mST = null ;
		ResultSet mRS = null ;
		int n_op_seq = fnGetOperationSeq() ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = " SELECT agent_default_subtime_range, customer_default_subtime_range " +
					" FROM t_sysparam_df_guideline " +
					" WHERE use_yn=1 and (oplog_seq=" + n_op_seq + " or oplog_seq is null) ; " ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = " SELECT agent_default_subtime_range, customer_default_subtime_range " +
					" FROM t_sysparam_df_guideline " +
					" WHERE use_yn=1 and (oplog_seq=" + n_op_seq + " or oplog_seq is null);" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = " SELECT agent_default_subtime_range, customer_default_subtime_range " +
					" FROM t_sysparam_df_guideline " +
					" WHERE use_yn=1 and (oplog_seq=" + n_op_seq + " or oplog_seq is null);" ;
		try {
			mST = mConnection.createStatement() ;
			if ((mRS = mST.executeQuery(query)) != null)
				while(mRS.next()) {
					sysGD.agent_default_subtime_range = mRS.getInt("agent_default_subtime_range") ;
					sysGD.customer_default_subtime_range = mRS.getInt("customer_default_subtime_range") ;
				}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	// ____________________________________________________________________________________________________
	// Emotion Engine / t_Ryyyymmdd

	//// CREATE_STATEMENT to PREPARE_STATEMENT // 2014.03.26
	public void fnSetResult(ArrayList<String> inQuery)
	{
		PreparedStatement mST = null ;
		ResultSet mRS = null ;
		String mDate = this.fnGetYYYYMMDD() ;
		String mTable = "t_engine_R" + mDate ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = "CALL sp_create_t_engine_Ryyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = "CALL sp_create_t_engine_Ryyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = "EXEC sp_create_t_engine_Ryyyymmdd '" + mDate + "';" ;
		fnExecuteInsertOrUpdateQuery(query) ;
		try {
			query = " INSERT INTO " + mTable + " (callkey, subTIME, rSTATE, rSIDE, rANGER, rNEUTRAL, rENERGY, rANGERCNT, rNEUTRALCNT, rMANANGER, rMANNEUTRAL, rWOMANANGER, rWOMANNEUTRAL) " +
					" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ;" ;
			mST = mConnection.prepareStatement(query) ;

			int iMAX = inQuery.size() ;
			for (int i=0; i<iMAX; i++)
			{
				String [] stQuery = inQuery.get(0).split(",") ;
				mST.setInt(1, Integer.valueOf(stQuery[0])) ;
				mST.setInt(2, Integer.valueOf(stQuery[1])) ;
				mST.setInt(3, Integer.valueOf(stQuery[2])) ;
				mST.setInt(4, Integer.valueOf(stQuery[3])) ;
				mST.setDouble(5, Double.valueOf(stQuery[4])) ;
				mST.setDouble(6, Double.valueOf(stQuery[5])) ;
				mST.setDouble(7, Double.valueOf(stQuery[6])) ;
				mST.setInt(8, Integer.valueOf(stQuery[7])) ;
				mST.setInt(9, Integer.valueOf(stQuery[8])) ;
				mST.setDouble(10, Double.valueOf(stQuery[9])) ;
				mST.setDouble(11, Double.valueOf(stQuery[10])) ;
				mST.setDouble(12, Double.valueOf(stQuery[11])) ;
				mST.setDouble(13, Double.valueOf(stQuery[12])) ;
				mST.addBatch() ; // NEW_BATCH
				mST.clearParameters() ; // 
				inQuery.remove(0) ;
			}
			mST.executeBatch() ; // NEW_BATCH
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
	
	//// NEW FUNCTION for EMORAY v3.0
	// ____________________________________________________________________________________________________
	// Emotion Engine Table Insert | V Table

	public void fnInsertVoiceEngineV(ArrayList<vasUnit> inData)
	{
		PreparedStatement mST = null ;
		String mDate = this.fnGetYYYYMMDD() ;
		String mTable = "t_engine_V" + mDate ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = "CALL sp_create_t_engine_Vyyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = "CALL sp_create_t_engine_Vyyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = "EXEC sp_create_t_engine_Vyyyymmdd'" + mDate + "';" ;
		fnExecuteInsertOrUpdateQuery(query) ;
		try {
			query = " INSERT INTO " + mTable + " (callkey, subtime, status, voice1_cnt, voice2_cnt, nonvoice_cnt, voice1_idx, voice2_idx, nonvoice_idx) " +
					" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ;" ;
			mST = mConnection.prepareStatement(query) ;
			int iMAX = inData.size() ;
			for (int i=0; i<iMAX; i++)
			{
				mST.setInt(1, Integer.valueOf(inData.get(i).callkey())) ;
				mST.setInt(2, Integer.valueOf(inData.get(i).subtime())) ;
				mST.setInt(3, Integer.valueOf(inData.get(i).status())) ;
				mST.setInt(4, Integer.valueOf(inData.get(i).voice1_cnt())) ;
				mST.setInt(5, Integer.valueOf(inData.get(i).voice2_cnt())) ;
				mST.setInt(6, Integer.valueOf(inData.get(i).nonvoice_cnt())) ;
				mST.setDouble(7, Double.valueOf(inData.get(i).voice1_idx())) ;
				mST.setDouble(8, Double.valueOf(inData.get(i).voice2_idx())) ;
				mST.setDouble(9, Double.valueOf(inData.get(i).nonvoice_idx())) ;
				mST.addBatch() ;
				mST.clearParameters() ;
			}
			mST.executeBatch() ;
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
	
	public void fnInsertVoiceEngineVS(ArrayList<vasUnit> inData)
	{
		PreparedStatement mST = null ;
		String mDate = this.fnGetYYYYMMDD() ;
		String mTable = "t_engine_VS" + mDate ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = "CALL sp_create_t_engine_VSyyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = "CALL sp_create_t_engine_VSyyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = "EXEC sp_create_t_engine_VSyyyymmdd'" + mDate + "';" ;
		fnExecuteInsertOrUpdateQuery(query) ;
		try {
			query = " INSERT INTO " + mTable + " (callkey, subtime, status, voice1_cnt, voice2_cnt, nonvoice_cnt, voice1_idx, voice2_idx, nonvoice_idx) " +
					" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ;" ;
			mST = mConnection.prepareStatement(query) ;
			int iMAX = inData.size() ;
			for (int i=0; i<iMAX; i++)
			{
				mST.setInt(1, Integer.valueOf(inData.get(i).callkey())) ;
				mST.setInt(2, Integer.valueOf(inData.get(i).subtime())) ;
				mST.setInt(3, Integer.valueOf(inData.get(i).status())) ;
				mST.setInt(4, Integer.valueOf(inData.get(i).voice1_cnt())) ;
				mST.setInt(5, Integer.valueOf(inData.get(i).voice2_cnt())) ;
				mST.setInt(6, Integer.valueOf(inData.get(i).nonvoice_cnt())) ;
				mST.setDouble(7, Double.valueOf(inData.get(i).voice1_idx())) ;
				mST.setDouble(8, Double.valueOf(inData.get(i).voice2_idx())) ;
				mST.setDouble(9, Double.valueOf(inData.get(i).nonvoice_idx())) ;
				mST.addBatch() ;
				mST.clearParameters() ;
			}
			mST.executeBatch() ;
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	// ____________________________________________________________________________________________________
	// Emotion Engine Table Insert | Y Table

	public void fnInsertVoiceEngineY(ArrayList<vasResult> inData)
	{
		PreparedStatement mST = null ;
		String mDate = this.fnGetYYYYMMDD() ;
		String mTable = "t_engine_Y" + mDate ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = "CALL sp_create_t_engine_Yyyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = "CALL sp_create_t_engine_Yyyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = "EXEC sp_create_t_engine_Yyyyymmdd'" + mDate + "';" ;
		fnExecuteInsertOrUpdateQuery(query) ;
		try {
			query = " INSERT INTO " + mTable + " (callkey, status, subtime, section) " +
					" VALUES (?, ?, ?, ?) ;" ;
			mST = mConnection.prepareStatement(query) ;
			int iMAX = inData.size() ;
			for (int i=0; i<iMAX; i++)
			{
				mST.setInt(1, Integer.valueOf(inData.get(0).callkey())) ;
				mST.setInt(2, Integer.valueOf(inData.get(0).status())) ;
				mST.setInt(3, Integer.valueOf(inData.get(0).subtime())) ;
				mST.setInt(4, Integer.valueOf(inData.get(0).section())) ;
				mST.addBatch() ;
				mST.clearParameters() ;
				inData.remove(0) ;
			}
			mST.executeBatch() ;
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
	
	public void fnInsertVoiceEngineYS(ArrayList<vasResult> inData)
	{
		PreparedStatement mST = null ;
		String mDate = this.fnGetYYYYMMDD() ;
		String mTable = "t_engine_YS" + mDate ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = "CALL sp_create_t_engine_YSyyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = "CALL sp_create_t_engine_YSyyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = "EXEC sp_create_t_engine_YSyyyymmdd'" + mDate + "';" ;
		fnExecuteInsertOrUpdateQuery(query) ;
		try {
			query = " INSERT INTO " + mTable + " (callkey, status, subtime, section) " +
					" VALUES (?, ?, ?, ?) ;" ;
			mST = mConnection.prepareStatement(query) ;
			int iMAX = inData.size() ;
			for (int i=0; i<iMAX; i++)
			{
				mST.setInt(1, Integer.valueOf(inData.get(0).callkey())) ;
				mST.setInt(2, Integer.valueOf(inData.get(0).status())) ;
				mST.setInt(3, Integer.valueOf(inData.get(0).subtime())) ;
				mST.setInt(4, Integer.valueOf(inData.get(0).section())) ;
				mST.addBatch() ;
				mST.clearParameters() ;
				inData.remove(0) ;
			}
			mST.executeBatch() ;
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}


	// ____________________________________________________________________________________________________
	// Emotion Engine Table Update | Y.section > R.side

	public void fnUpdateEmotionSide(ArrayList<int[]>SEC_FIX, int callkey)
	{
		String query = "" ;
		String RT = "t_engine_R" + this.fnGetYYYYMMDD() ;

		int subtime = 1 ;
		int side = 0 ;
		int dur = 0 ;

		PreparedStatement mST = null ;
		try {
			query = " UPDATE " + RT + 
					" SET " + RT + ".rSIDE = " + "?" + 
					" WHERE " + RT + ".callkey = ? AND " + RT + ".subTIME = ? ;" ;
			mST = mConnection.prepareStatement(query) ;
			int iMAX = SEC_FIX.size() ;
			for (int i=0; i<iMAX; i++)
			{
				side = SEC_FIX.get(i)[0] ;
				dur = SEC_FIX.get(i)[1] ;
				for (int j=0; j<dur; j++)
				{
					mST.setInt(1, Integer.valueOf(side)) ;
					mST.setInt(2, Integer.valueOf(callkey)) ;
					mST.setInt(3, Integer.valueOf(subtime)) ;
					mST.addBatch() ;
					mST.clearParameters() ;
					subtime++ ;
				}
			}
			mST.executeBatch() ;
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
	
	// _______________________________
	// Emotion Engine / weight2DSP_SVM

	public void GETweight(double [] weight, int level)
	{
		String mQuery = "SELECT * FROM weight2DSP_SVM"  + String.valueOf(level) + ";"  ;
		Statement mST = null ;
		ResultSet mRS = null ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(mQuery) ;
			int mINDEX = 0 ;
			while(mRS.next()) {
				weight[mINDEX] =  Double.valueOf(mRS.getString("val"));
				mINDEX++ ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	// _____________________________
	// Emotion Engine / xsup2DSP_SVM

	public void GETxsup(double [][] xsup, int level)
	{
		String mQuery = "SELECT * FROM xsup2DSP_SVM" + String.valueOf(level) + ";" ;
		Statement mST = null ;
		ResultSet mRS = null ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(mQuery) ;
			int mINDEX = 0 ;
			int dim =  xsup[0].length ;
			while(mRS.next()) {
				for(int i=0 ; i < dim ; i++) { xsup[mINDEX][i] = Double.valueOf(mRS.getString("val" + String.valueOf(i+1))) ; }
				mINDEX++ ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
}
